/*
Gets the document data for the document at the given path INCLUDING the blob. Security IS checked. The first
segment of the documentPath should be the DocumentRoot name. The last segment should be the DocumentName
Individual segments are separated by the forward slash (/)
*/
CREATE PROC [dbo].[asi_DocumentMainByPath]
   @documentPath nvarchar(2000),
   @organizationKey uniqueidentifier,
   @userKey uniqueidentifier,
   @loggedInUserGroupKey uniqueidentifier = '00000000-0000-0000-0000-000000000000', -- if this is empty, we assume the user is not logged in
   @publishedOnly bit = 0,
   @ignoreLicensing bit = 0
AS
BEGIN
   DECLARE
      @rootHierarchyKey uniqueidentifier,
      @hierarchyKey uniqueidentifier,
      @documentVersionKey uniqueidentifier,
      @documentKey uniqueidentifier

   EXEC asi_DocumentKeysByPath @documentPath, @organizationKey, @userKey, @loggedInUserGroupKey, @ignoreLicensing, @rootHierarchyKey out, @hierarchyKey out, @documentVersionKey out, @documentKey out

   IF @documentVersionKey IS NOT NULL
      SELECT TOP 1
             a.Blob,
             a.DocumentKey,
             a.DocumentVersionKey,
             a.DocumentStatusCode,
             a.DocumentName,
             a.AlternateName,
             a.DocumentDescription,
             a.DocumentTypeCode,
             a.IsSystem,
             a.ContainsChildrenFlag,
             a.AccessKey,
             a.DefaultChildAccessKey,
             a.StatusUpdatedOn,
             a.StatusUpdatedByUserKey,
             a.CreatedOn,
             a.CreatedByUserKey,
             a.UpdatedOn,
             a.UpdatedByUserKey,
             b.DocumentTypeName,
             b.DocumentTypeDesc,
             b.DocumentIconURL,
             @hierarchyKey AS HierarchyKey
        FROM DocumentMain a INNER JOIN DocumentTypeRef b on a.DocumentTypeCode = b.DocumentTypeCode
       WHERE a.DocumentVersionKey = @documentVersionKey
         AND (a.DocumentStatusCode IN (40,60) OR @publishedOnly = 0)
         AND EXISTS(
             SELECT 1
               FROM AccessItem INNER JOIN UserToken ON AccessItem.Grantee = UserToken.Grantee OR AccessItem.Grantee = @loggedInUserGroupKey
              WHERE AccessItem.AccessKey = a.AccessKey
                AND UserToken.UserKey = @userKey)
       ORDER BY a.CreatedOn DESC
END


GO